from flaskDemo.sql.mysql_db import mysql_db
from flaskDemo.config.config import Config
from flask import request
from flaskDemo.token.token_deal import verify_token
import datetime
class SqlModel():
    table_name = None
    table_values = []
    def __init__(self, table_name,table_values):
        self.table_name = table_name
        self.table_values = table_values
        self.columnKeys = self.getMenuModel()

    def getMenuModel(self):
        sql = f"select * from information_schema.COLUMNS where table_name = '{self.table_name}' and COLUMN_KEY != 'PRI'"
        with mysql_db() as db:
            result = db.fetch_all(sql)
            columnKey = []
            for item in result:
                columnKey.append(item['COLUMN_NAME'])
            return columnKey

    def insert_sql(self):
        sql = f"insert into {self.table_name} ("
        cnt = 0
        for columnKey in self.columnKeys:
            if cnt == len(self.columnKeys) - 1:
                sql = sql + columnKey + ") values ("
            else:
                sql = sql + columnKey + ","
            cnt = cnt + 1
        for value in self.table_values:
            for key in self.columnKeys:
                if key in value:
                    item_value = value[key]
                    if item_value is not None:
                        if type(item_value) == str:
                            sql = f"{sql} '{self.insertDefaultValue(key,item_value)}',"
                        else:
                            sql = f"{sql} {item_value},"
                    else:
                        sql = f"{sql}  NULL ,"
                else:
                    sql = f"{sql}  NULL ,"
        sql = sql[0:-1] + ")"
        return sql

    def getCurrentUser(self,auth):
        token = auth[7:]
        return verify_token(token)

    def insertDefaultValue(self, key, value):
        auth = request.headers.get("Authorization")
        currentUser = self.getCurrentUser(auth)
        print(Config.CREATE_TIME)
        if key == Config.CREATE_TIME:
            return datetime.now()
        elif key == Config.CREATE_BY:
            return currentUser.get("id")
        else:
            return value

    def updateDefaultValue(self, key, value):
        auth = request.headers.get("Authorization")
        currentUser = self.getCurrentUser(auth)
        if key == Config.UPDATE_TIME:
            return datetime.now()
        elif key == Config.UPDATE_BY:
            return currentUser.get("id")
        else:
            return value

    def insert_batch_sql(self):
        sql = f"insert into {self.table_name} ("
        cnt = 0
        for columnKey in self.columnKeys:
            if cnt == len(self.columnKeys) - 1:
                sql = sql + columnKey + ") values ("
            else:
                sql = sql + columnKey + ","
            cnt = cnt + 1
        for value in self.table_values:
            for key in self.columnKeys:
                if key in value:
                    item_value = value[key]
                    if item_value is not None:
                        if type(item_value) == str:
                            sql = f"{sql} '{self.insertDefaultValue(key,item_value)}',"
                        else:
                            sql = f"{sql} {item_value},"
                    else:
                        sql = f"{sql} NULL ,"
                else:
                    sql = f"{sql} NULL ,"
            sql = sql[0:-1] + "),("
        sql = sql[0:-2] + ";"
        return sql

    def update_sql(self):
        sql = f"update {self.table_name} set "
        for value in self.table_values:
            for key in self.columnKeys:
                if key in value:
                    item_value = value[key]
                    if item_value is not None:
                        if type(item_value) == str:
                            sql = f"{sql} {key} = '{value[key]}',"
                        else:
                            sql = f"{sql} {key} = {value[key]},"
                    else:
                        sql = f"{sql} {key} = NULL ,"
                else:
                    sql = f"{sql} {key} = NULL ,"
        sql = sql[0:-1]
        return sql

    def delete_sql(self):
        sql = f"delete from {self.table_name} "
        return sql

